﻿using AchieveCommon;
using AchieveDALFactory;
using AchieveEntity;
using AchieveInterfaceDAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Timers;

namespace AchieveBLL
{
    public class CockpitBLL
    {
        //private static int validTime;

        public static int ValidTime
        {
            get {
                //默认数据60分钟过期
                return  string.IsNullOrWhiteSpace(ConfigHelper.ReadConfig("validTime")) ? 60 : Convert.ToInt32(ConfigHelper.ReadConfig("validTime")); //默认数据30分钟过期
                  }
            //set { CockpitBLL.validTime = value; }
        }
        //public static readonly string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        public CockpitBLL()
        {
            //string t = ConfigHelper.ReadConfig("validTime");
            //validTime = string.IsNullOrWhiteSpace(t) ? 30 : Convert.ToInt32(t); //默认数据30分钟过期
        }
        /// <summary>
        /// 求有效工艺时间，根据区间日期比例分配工时
        /// </summary>
        /// <param name="operGroupID">工艺组日期</param>
        /// <param name="startdate">统计区间开始日期</param>
        /// <param                           
        /// name="enddate">统计区间结束日期</param>
        /// <returns></returns>
        //public float operValue(int operGroupID, DateTime startdate, DateTime enddate, bool removefinished)
        //{
        //    float opervalue = 0;
        //    //查询需要统计的工艺编码；
        //    string sql = "select operid from tbOper where OperGroupID=" + operGroupID;
        //    DataTable dtoperid = SqlHelper.GetDataTable(SqlHelper.connStr, sql);
        //    string where = " 1=1";
        //    string sqloper = string.Empty;
        //    DataTable dtoper = new DataTable();
        //    //从ieman数据库中查询预估工时,1037预估工时
        //    if (operGroupID > 2000)
        //    {
        //        //条件1：运行时间选择，存在预估工时；
        //        where += " and EstWorkTime > 0 ";
        //        //条件2：时间范围选择，计划时间与统计区间存在交集！
        //        where += " and PSTime <='" + enddate.ToString("yyyy-MM-dd") + "'";
        //        where += " and PETime >='" + startdate.ToString("yyyy-MM-dd") + "'";
        //        if (operGroupID == 2001)//预估工时，不区分是否确认,暂时不用
        //        {
        //            sqloper = "select PSTime as FPlanStartDate,PETime as FPlanEndDate,EstWorkTime as Ftotalworktime from tbMgrNodeInfo where  " + where;
        //            dtoper = SqlHelper.GetDataTable(SqlHelper.connStr, sqloper);
        //        }
        //        else if (operGroupID == 2002)//已确认的预估工时
        //        {
        //            where += " and tbProject.status=1 ";
        //            sqloper = "select PSTime as FPlanStartDate,PETime as FPlanEndDate,EstWorkTime as Ftotalworktime from tbMgrNodeInfo join tbProject on tbMgrNodeInfo.ProjectID = tbProject.ProjectID where  " + where;
        //            dtoper = SqlHelper.GetDataTable(SqlHelper.connStr, sqloper);
        //        }
        //        else if (operGroupID == 2003)//未确认的预估工时
        //        {
        //            where += " and tbProject.status=0 ";
        //            sqloper = "select PSTime as FPlanStartDate,PETime as FPlanEndDate,EstWorkTime as Ftotalworktime from tbMgrNodeInfo join tbProject on tbMgrNodeInfo.ProjectID = tbProject.ProjectID where  " + where;
        //            dtoper = SqlHelper.GetDataTable(SqlHelper.connStr, sqloper);
        //        }
        //        else if (operGroupID == 2004)//委外的工时
        //        {
        //            where = " 1=1";
        //            //条件1：运行时间为正
        //            where += " and Ftotalworktime > 0 ";
        //            //条件2：计划时间区间与统计区间存在交集
        //            where += " and FPlanStartDate <='" + enddate.ToString("yyyy-MM-dd") + "'";
        //            where += " and FPlanEndDate >='" + startdate.ToString("yyyy-MM-dd") + "'";
        //            //条件3：选择委外=16015，忽略fworkcenterID制造部=15293
        //            where += " and fworkcenterID =16015 ";
        //            if (removefinished)
        //            {
        //                where += "and fstatus <3 ";//仅查询未完成的工单
        //            }

        //            //在k3数据库中查询工序计划表，委外工时
        //            sqloper = "select FPlanStartDate,FPlanEndDate,Ftotalworktime,ffinishtime from SHworkbillENTRY where  " + where;
        //            dtoper = SqlHelper.GetDataTable(SqlHelper.connStrK3, sqloper);
        //        }

        //    }
        //    else
        //    {
        //        //条件1：运行时间为正
        //        where += " and Ftotalworktime > 0 ";
        //        //条件2：计划时间区间与统计区间存在交集
        //        where += " and FPlanStartDate <='" + enddate.ToString("yyyy-MM-dd") + "'";
        //        where += " and FPlanEndDate >='" + startdate.ToString("yyyy-MM-dd") + "'";
        //        //条件3：选择fworkcenterID制造部=15293，忽略委外=16015
        //        where += " and fworkcenterID =15293 ";
        //        if (removefinished)
        //        {
        //            where += "and fstatus <3 ";//过滤已完成订单，仅查询未完成的工单
        //        }
        //        //在k3数据库中查询工序计划表
        //        //选择工艺组内的所有工艺          
        //        for (int i = 0; i < dtoperid.Rows.Count; i++)
        //        {
        //            if (i == 0)
        //            {
        //                where += " and ( foperid=" + dtoperid.Rows[i][0].ToString();
        //            }
        //            else
        //            {
        //                where += " or foperid=" + dtoperid.Rows[i][0].ToString();
        //            }
        //            if (i == dtoperid.Rows.Count - 1)
        //            {
        //                where += ")";
        //            }
        //        }
        //        //在k3数据库中查询工序计划表
        //        if (removefinished)
        //        {//当过滤已完成工单时，同时减去未完成工单中的已完成部分ffinishtime
        //            sqloper = "select FPlanStartDate,FPlanEndDate,(Ftotalworktime-ffinishtime) as Ftotalworktime from SHworkbillENTRY where  " + where;
        //        }
        //        else
        //        {
        //            sqloper = "select FPlanStartDate,FPlanEndDate,Ftotalworktime from SHworkbillENTRY where  " + where;
        //        }

        //        dtoper = SqlHelper.GetDataTable(SqlHelper.connStrK3, sqloper);
        //    }

        //    foreach (DataRow item in dtoper.Rows)
        //    {
        //        Single ft = Convert.ToSingle(item["Ftotalworktime"]);
        //        if (ft>0)//只考虑正值的工时，当计划工时减已完成工时时，偶尔会出现负值，
        //        {
        //            //跨区间的工作量平均分配
        //        DateTime sd = Convert.ToDateTime(item["FPlanStartDate"]);
        //        DateTime ed = Convert.ToDateTime(item["FPlanEndDate"]);
        //        //求区间内天数
        //        DateTime scd = (sd <= startdate ? startdate : sd);
        //        DateTime ecd = (ed >= enddate ? enddate : ed);
        //        int statdays = ecd.Subtract(scd).Days + 1;
        //        int totaldays = ed.Subtract(sd).Days + 1;//当天结束也算一天
        //        opervalue += Convert.ToSingle(item["Ftotalworktime"]) * statdays / totaldays;
        //        }

        //    }
        //    return opervalue;
        //}
        /// <summary>
        /// 工时计算函数，通用，2019-4-11，ben
        /// </summary>
        /// <param name="operGroupID"></param>
        /// <param name="startdate"></param>
        /// <param name="enddate"></param>
        /// <param name="removefinished">不统计已完成订单</param>
        /// <param name="department">department参数表示部门，分部门统计数据；sw,ml,ec,sd,mf,all </param>
        /// <param name="schedule">影响数据表取值，实际计划：plan，自动排产：autocal</param>
        /// <returns></returns>
        public static double operValue(int operGroupID, DateTime startdate, DateTime enddate, bool removefinished, string department, string schedule = "plan")
        {
            double opervalue = 0;
            string where = " 1=1";            
            //条件1：运行时间选择，存在预估工时；
            where += " and EstWorkTime > 0 ";
            //条件2：时间范围选择，计划时间与统计区间存在交集！
            where += " and PSTime <='" + enddate.ToString("yyyy-MM-dd") + "'";
            where += " and PETime >='" + startdate.ToString("yyyy-MM-dd") + "'";
            string sqloper = string.Empty;
            DataTable dtoper = new DataTable();

            switch (operGroupID)
            {
                case 2001://预估工时，不区分是否确认,暂时不用                   
                     sqloper = "select PSTime as FPlanStartDate,PETime as FPlanEndDate,EstWorkTime as Ftotalworktime from tbMgrNodeInfo where  " + where;
                    dtoper = SqlHelper.GetDataTable(SqlHelper.connStr, sqloper);
                    break;
                case 2002://已确认的预估工时       
                       where += " and tbProject.status=1 ";
                      switch (department)
                    {
                        case "sw": where += " and tbProject.Department like '钢水%'";
                            break;
                        case "ec": where += " and tbProject.Department like '电缆%'";
                            break;
                        case "mf": where += " and tbProject.Department like '制造%'";
                            break;
                        case "sd": where += " and tbProject.Department like '能源%'";
                            break;
                        case "ml": where += " and tbProject.Department like '冶金%'";
                            break;
                    }
                    sqloper = "select PSTime as FPlanStartDate,PETime as FPlanEndDate,EstWorkTime as Ftotalworktime from tbMgrNodeInfo join tbProject on tbMgrNodeInfo.ProjectID = tbProject.ProjectID where  " + where;
                   dtoper = SqlHelper.GetDataTable(SqlHelper.connStr, sqloper);
                    break;
                case 2003://未确认的预估工时
                     switch (department)
                    {
                        case "sw": where += " and tbProject.Department like '钢水%'";
                            break;
                        case "ec": where += " and tbProject.Department like '电缆%'";
                            break;
                        case "mf": where += " and tbProject.Department like '制造%'";
                            break;
                        case "sd": where += " and tbProject.Department like '能源%'";
                            break;
                        case "ml": where += " and tbProject.Department like '冶金%'";
                            break;
                    } 
                    where += " and tbProject.status=0 ";
                    sqloper = "select PSTime as FPlanStartDate,PETime as FPlanEndDate,EstWorkTime as Ftotalworktime from tbMgrNodeInfo join tbProject on tbMgrNodeInfo.ProjectID = tbProject.ProjectID where  " + where;
                    dtoper = SqlHelper.GetDataTable(SqlHelper.connStr, sqloper);
                    break;
                case 2004://委外工时
                    where = " 1=1";
                    //条件1：运行时间为正
                    where += " and a.Ftotalworktime > 0 ";
                    //条件2：计划时间区间与统计区间存在交集
                    if (schedule == "autocal")//FEntrySelfz0374,FEntrySelfz0375
                    {
                        where += " and a.FEntrySelfz0374 <='" + enddate.ToString("yyyy-MM-dd") + "'";
                        where += " and a.FEntrySelfz0375 >='" + startdate.ToString("yyyy-MM-dd") + "'";
                    }
                    else { 
                     where += " and a.FPlanStartDate <='" + enddate.ToString("yyyy-MM-dd") + "'";
                     where += " and a.FPlanEndDate >='" + startdate.ToString("yyyy-MM-dd") + "'";
                    }
                   
                    //条件3：选择委外=16015，忽略fworkcenterID制造部=15293
                    where += " and a.fworkcenterID =16015 ";
                    if (removefinished)
                    {
                        where += "and a.fstatus <3 ";//仅查询未完成的工单
                    } 
                    switch (department)
                    {
                        case "sw": where += " and b.FHeadSelfz0324 like '钢水控流事业部%'";
                            break;
                        case "ec": where += " and b.FHeadSelfz0324 like '电缆卷筒事业部%'";
                            break;
                        case "mf": where += " and b.FHeadSelfz0324 like '制造事业部%'";
                            break;
                        case "sd": where += " and b.FHeadSelfz0324 like '能源环保事业部%'";
                            break;
                        case "ml": where += " and b.FHeadSelfz0324 like '冶金工程事业部%'";
                            break;
                    }
                    //在k3数据库中查询工序计划表，委外工时
                    // sqloper = "select FPlanStartDate,FPlanEndDate,Ftotalworktime,ffinishtime from SHworkbillENTRY where  " + where;
                    if (schedule == "autocal")//FEntrySelfz0374,FEntrySelfz0375
                    {
                        sqloper = "select FEntrySelfz0374 as FPlanStartDate,FEntrySelfz0375 as FPlanEndDate,a.Ftotalworktime,a.ffinishtime,a.FEntrySelfz0373,b.FHEADSelfz0324 from SHworkbillENTRY a left join shworkbill b on a.FEntrySelfz0373=b.ficmono where " + where;
                   
                    }
                    else {
                        sqloper = "select a.FPlanStartDate,a.FPlanEndDate,a.Ftotalworktime,a.ffinishtime,a.FEntrySelfz0373,b.FHEADSelfz0324 from SHworkbillENTRY a left join shworkbill b on a.FEntrySelfz0373=b.ficmono where " + where;
                   
                    }
                     dtoper = SqlHelper.GetDataTable(SqlHelper.connStrK3, sqloper);
                    break;

                default://其他正常的工序工时统计
                    where = " 1=1";
                    //条件1：运行时间为正
                    where += " and a.Ftotalworktime > 0 ";

                    //条件2：计划时间区间与统计区间存在交集
                    if (schedule == "autocal")//FEntrySelfz0374,FEntrySelfz0375，自动排产取另外字段；
                    {
                        where += " and a.FEntrySelfz0374 <='" + enddate.ToString("yyyy-MM-dd") + "'";
                        where += " and a.FEntrySelfz0375 >='" + startdate.ToString("yyyy-MM-dd") + "'";
                    }
                    else
                    {
                        where += " and a.FPlanStartDate <='" + enddate.ToString("yyyy-MM-dd") + "'";
                        where += " and a.FPlanEndDate >='" + startdate.ToString("yyyy-MM-dd") + "'";
                    }                    
                    //条件3：选择fworkcenterID制造部=15293，忽略委外=16015
                    where += " and a.fworkcenterID =15293 ";
                    if (removefinished)
                    {
                        where += "and a.fstatus <3 ";//过滤已完成订单，仅查询未完成的工单
                    }
                    switch (department)
                    { 
                        case "sw": where += " and b.FHeadSelfz0324 like '钢水控流事业部%'";
                            break;
                        case "ec": where += " and b.FHeadSelfz0324 like '电缆卷筒事业部%'";
                            break;
                        case "mf": where += " and b.FHeadSelfz0324 like '制造事业部%'";
                            break;
                        case "sd": where += " and b.FHeadSelfz0324 like '能源环保事业部%'";
                            break;
                        case "ml": where += " and b.FHeadSelfz0324 like '冶金工程事业部%'";
                            break;
                    }
                    //在k3数据库中查询工序计划表
                    //查询需要统计的工艺编码；
                    string sql = "select operid from tbOper where OperGroupID=" + operGroupID;
                    DataTable dtoperid = SqlHelper.GetDataTable(SqlHelper.connStr, sql);
                    //选择工艺组内的所有工艺      a.foperid in(,,)    
                    for (int i = 0; i < dtoperid.Rows.Count; i++)
                    {
                        if (i == 0)
                        {
                            where += " and ( a.foperid=" + dtoperid.Rows[i][0].ToString();
                        }
                        else
                        {
                            where += " or a.foperid=" + dtoperid.Rows[i][0].ToString();
                        }
                        if (i == dtoperid.Rows.Count - 1)
                        {
                            where += ")";
                        }
                    }
                    if (schedule == "autocal")//FEntrySelfz0374,FEntrySelfz0375
                    {
                        sqloper = "select FEntrySelfz0374 as FPlanStartDate,FEntrySelfz0375 as FPlanEndDate,";
                    }
                    else
                    {
                        sqloper = "select a.FPlanStartDate,a.FPlanEndDate,";
                    }                

                    //在k3数据库中查询工序计划表
                    if (removefinished)
                    {//当过滤已完成工单时，同时减去未完成工单中的已完成部分ffinishtime

                        sqloper = sqloper+"(a.Ftotalworktime-a.ffinishtime) as Ftotalworktime from SHworkbillENTRY a left join shworkbill b on a.FEntrySelfz0373=b.ficmono where " + where;
                      }
                    else
                    {
                        sqloper = sqloper+"a.Ftotalworktime from SHworkbillENTRY a left join shworkbill b on a.FEntrySelfz0373=b.ficmono where " + where;
                    }

                    dtoper = SqlHelper.GetDataTable(SqlHelper.connStrK3, sqloper);
                    break;   
            }

          
            //查询结果进一步处理；将工时分摊到统计区间。
            foreach (DataRow item in dtoper.Rows)
            {
                try
                {
                                 double ft = Convert.ToDouble(item["Ftotalworktime"]);
                                if (ft > 0)//只考虑正值的工时，当计划工时减已完成工时时，偶尔会出现负值，
                                {
                                    //跨区间的工作量平均分配
                                    DateTime sd = Convert.ToDateTime(item["FPlanStartDate"]);
                                    DateTime ed = Convert.ToDateTime(item["FPlanEndDate"]);
                                    //求区间内天数
                                    DateTime scd = (sd <= startdate ? startdate : sd);
                                    DateTime ecd = (ed >= enddate ? enddate : ed);
                                    int statdays = ecd.Subtract(scd).Days + 1;//该工序在统计区间内所占天数
                                    int totaldays = ed.Subtract(sd).Days + 1;//该工序的总天数，总计划执行天数，当天结束也算一天
                                    opervalue += Convert.ToDouble(item["Ftotalworktime"]) * statdays / totaldays;
                                }
                }
                catch (Exception)
                {
                    continue;
                    //throw;
                }
              
            }
            return opervalue;
        }
              
     
       


     /// <summary>
     /// 项目预估工时计算函数，按月，2019-7-17，ben
     /// </summary>
     /// <param name="operGroupID"></param>
     /// <param name="startdate"></param>
     /// <param name="enddate"></param>
     /// <param name="removefinished">不统计已完成订单</param>
     /// <param name="department">部门</param>
     /// <returns></returns>
        //public static List<operTime> MonthEstOperValue(DateTime startdate, DateTime enddate, bool removefinished, string department, string schedule)
     public static List<operTime> MonthEstOperValue(DateTime startdate, DateTime enddate)
        { 
            List<operTime> operTimeS = new List<operTime>();
             operTime che = new operTime("车", 1001);
             operTime xi = new operTime("铣", 1002);             
             operTime mo = new operTime("磨", 1007);
             operTime qian = new operTime("钳", 1003);
             operTime tang = new operTime("镗", 1009);
             operTime jgzx = new operTime("加工中心", 1008);
             operTime maohan = new operTime("铆焊", 1010);
            
            double opervalue = 0;
            string sqloper = @"select cost,worktype,parttype,a.ProjectID,b.PSTime,b.PETime
                    from tbProjectCostEntry a left join tbMgrNodeInfo b on a.ProjectID=b.ProjectID ";
             //条件2：时间范围选择，计划时间与统计区间存在交集！
             sqloper += string.Format("where b.NodeID=5 and PSTime <='{0}'and PETime >='{1}'", enddate.ToString("yyyy-MM-dd"), startdate.ToString("yyyy-MM-dd"));
             DataTable dtcost = SqlHelper.GetDataTable(SqlHelper.connStr, sqloper); 

         // 查询结果进一步处理；将成本分摊到统计区间,并换算成各工序工时
             foreach (DataRow item in dtcost.Rows)
             {
                 try
                 {
                     double ft = Convert.ToDouble(item["cost"]);
                     if (ft > 0)//只考虑正值
                     {
                         //跨区间的工作量平均分配
                         DateTime sd = Convert.ToDateTime(item["PSTime"]);
                         DateTime ed = Convert.ToDateTime(item["PETime"]);
                         //求区间内天数
                         DateTime scd = (sd <= startdate ? startdate : sd);
                         DateTime ecd = (ed >= enddate ? enddate : ed);
                         int statdays = ecd.Subtract(scd).Days + 1;//该工序在统计区间内所占天数
                         int totaldays = ed.Subtract(sd).Days + 1;//该工序的总天数，总计划执行天数，当天结束也算一天
                         double cost = Convert.ToDouble(item["cost"]) * statdays / totaldays;
                         opervalue = cost / 25;//成本换算成总工时；
                         //根据加工件类型不同，工时分配到各工序
                         if (item["worktype"].ToString().Trim()=="铆焊件")//铆焊件类成本全部换算到铆焊工时；
	                        {
                              maohan.workTime+=opervalue;
		 
	                        }else{
                                        switch (item["parttype"].ToString().Trim())
                                     {
                                         case "轴类":
                                             che.workTime += opervalue * 0.7;
                                             xi.workTime += opervalue * 0.2;
                                             mo.workTime += opervalue * 0.1;
                                             break;
                                         case "框架类":
                                             che.workTime += opervalue * 0.1;
                                             xi.workTime += opervalue * 0.2;
                                             jgzx.workTime += opervalue * 0.6;
                                             mo.workTime += opervalue * 0.05;
                                             qian.workTime += opervalue * 0.05;
                                             break;
                                         case "平板类": 
                                             xi.workTime += opervalue * 0.8;
                                             mo.workTime += opervalue * 0.1;
                                             qian.workTime += opervalue * 0.1;
                                             break;
                                         case "端盖类":
                                             che.workTime += opervalue * 0.8;
                                             xi.workTime += opervalue * 0.1;
                                             qian.workTime += opervalue * 0.1;
                                             break;
                                         case "盘类":
                                             che.workTime += opervalue * 0.8;
                                             xi.workTime += opervalue * 0.1;
                                             qian.workTime += opervalue * 0.1;
                                             break;
                                         case "轴承座类":
                                             tang.workTime += opervalue * 0.7;
                                             xi.workTime += opervalue * 0.2;
                                             qian.workTime += opervalue * 0.1;
                                             break;
                                         case "箱体类":
                                             tang.workTime += opervalue * 0.7;
                                             xi.workTime += opervalue * 0.2;
                                             qian.workTime += opervalue * 0.1;
                                             break;                            
                                         default:
                                             break;
                                     }

                         }

                        
                     }
                 }
                 catch (Exception)
                 {
                     //continue;
                     throw;
                 }

             }
      
             operTimeS.Add(che);
             operTimeS.Add(xi);
             operTimeS.Add(mo);
             operTimeS.Add(qian);
             operTimeS.Add(tang);
             operTimeS.Add(jgzx);
             operTimeS.Add(maohan);
            
             return operTimeS;
        }

     

        /// <summary>
        /// 通用的产能预警计算方法
        /// </summary>
        /// <param name="startdate"></param>
        /// <param name="count"></param>
        /// <param name="refresh"></param>
        /// <param name="removefinished"></param>
        /// <param name="department"></param>
        /// <returns></returns>
        public static DataTable getWorkTime(string cycle, DateTime startdate, int count, bool refresh, bool removefinished, string department,string schedule, bool classifyOper,string dataType="data")
        {
            bool saveToDB = true;
            if (schedule == "autocal")//选取的自动排程
            {
                refresh = true;
                saveToDB = false;
            } 

            #region 单独处理预估月产能预警
            if (dataType=="monthOperEst")
            {
                DataTable dtgroup=new DataTable();
                 dtgroup.Columns.Add("id",typeof(int));
                 dtgroup.Columns.Add("OperGroupName",typeof(string));
                 dtgroup.Columns.Add("OperGroupID",typeof(int));
                 dtgroup.Columns.Add("DayTime",typeof(int));
                 dtgroup.Columns.Add("AlertValue",typeof(int));

                //表格按以下顺序
                 //operTime che = new operTime("车", 1001);
                 //operTime xi = new operTime("铣", 1002);
                 //operTime mo = new operTime("磨", 1007);
                 //operTime qian = new operTime("钳", 1003);
                 //operTime tang = new operTime("镗", 1009);
                 //operTime jgzx = new operTime("加工中心", 1008);
                 //operTime maohan = new operTime("铆焊", 1010);

                // dtgroup.Rows.Add(1,2,3,4,5);//直接添加数据
                 DataRow che = dtgroup.NewRow();
                 che[0] = 1;
                 che[1] = "车";
                 che[2] = 1001;
                 che[3] = 100;
                 che[4] = 70;
                 DataRow xi = dtgroup.NewRow();
                 xi[0] = 2;
                 xi[1] = "铣";
                 xi[2] = 1002;
                 xi[3] = 50;
                 xi[4] = 70;
                 DataRow mo = dtgroup.NewRow();
                 mo[0] = 3;
                 mo[1] = "磨";
                 mo[2] = 1007;
                 mo[3] = 10;
                 mo[4] = 70;
                 DataRow qian = dtgroup.NewRow();
                 qian[0] = 4;
                 qian[1] = "钳";
                 qian[2] = 1003;
                 qian[3] = 200;
                 qian[4] = 70;
                 DataRow tang = dtgroup.NewRow();
                 tang[0] = 5;
                 tang[1] = "镗";
                 tang[3] = 30;
                 tang[4] = 70;
                 DataRow jgzx = dtgroup.NewRow();
                 jgzx[0] = 6;
                 jgzx[1] = "加工中心";
                 jgzx[2] = 1008;
                 jgzx[3] = 150;
                 jgzx[4] = 70;
                
               
                 dtgroup.Rows.Add(che);
                 dtgroup.Rows.Add(xi);
                 dtgroup.Rows.Add(mo);
                 dtgroup.Rows.Add(qian);
                 dtgroup.Rows.Add(tang);
                 dtgroup.Rows.Add(jgzx);
                 dtgroup.Rows.Add(7, "铆焊", 1010, 400, 70);//直接添加数据

                for (int i = 0; i < count; i++)//逐周期处理
                {
                    string field = i.ToString();
                    dtgroup.Columns.Add(field);//添加数据记录列
                     DateTime sdate, edate;
                            sdate = startdate.AddMonths(i);
                            edate = sdate.AddMonths(1).AddDays(-1); 
                    //直接计算工序工时,按比例分配，因此同时计算；
                    List<operTime> operTimes= MonthEstOperValue(sdate,edate);
                    //待实现功能：查询结果存数据库；
                    for (int j = 0; j < dtgroup.Rows.Count; j++)
			        { 
			             dtgroup.Rows[j][field]=operTimes[j].workTime; 
			        }
                   

                    //dtgroup.Rows[0][field]="";
                  
                    //foreach (DataRow item in dtgroup.Rows)
                    //{
                    //    DateTime sdate, edate;
                    //        sdate = startdate.AddMonths(i);
                    //        edate = sdate.AddMonths(1).AddDays(-1); 
                    //    //if (!refresh)
                    //    //{
                    //        //从数据库取值，首先判断数据库值是否过期！
                    //        string sqldb = string.Format("select value, updatetime from  tbOperValue  where department='" + department + "' and type='monthEst' and opergroupid={0} and sdate='{1}' and removefinished= '{2}'  order by updatetime desc", Convert.ToInt32(item["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);
                    //        DataTable dt = SqlHelper.GetDataTable(SqlHelper.connStr, sqldb);
                    //        if (dt.Rows.Count > 0)
                    //        {//数据库中有历史计算值
                    //            object updatetime = dt.Rows[0]["updatetime"];
                    //            DateTime d1 = Convert.ToDateTime(updatetime);
                    //            DateTime d2 = DateTime.Now;
                    //            TimeSpan sp = d2.Subtract(d1);
                    //            if (sp.Minutes < ValidTime)//信息未过期
                    //            {
                    //                item[field] = dt.Rows[0]["value"];
                    //                continue;
                    //            }
                    //        }
                    //    }
                        //计算产能并刷新数据库记录
                        //item[field] = operValue(Convert.ToInt32(item["OperGroupID"]), sdate, edate, removefinished, department, schedule);//调用工时值计算主函数
                        // item[field] = operValue(Convert.ToInt32(item["OperGroupID"]), sdate, edate, removefinished);//调用工时值计算主函数
                        //if (saveToDB)
                        //{    //删除陈旧数据，插入新数据
                        //    string sqldel = string.Format("delete from  tbOperValue where type='" + cycle + "'and department='" + department + "' and opergroupid={0} and sdate='{1}' and removefinished='{2}'", Convert.ToInt32(item["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);
                        //    SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sqldel);
                        //    string sql3 = string.Format("insert into tbOperValue(type,sdate,opergroupid,value,updatetime,removefinished,department) values('" + cycle + "','{0}',{1},{2},'{3}','{4}','{5}')", sdate.ToString("yyyy-MM-dd"), Convert.ToInt32(item["OperGroupID"]), item[field], DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), removefinished, department);
                        //    SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sql3);
                        //}
                    //}
                }
                return dtgroup; 

            }

            #endregion
               
                if (classifyOper)//类型：分工艺
                { 
                #region 类型：分工艺
                 string where = " OperGroupID<2000 and ischeck=1";
                    
                 string sql = "select OperGroupName,OperGroupID,DayTime,AlertValue from tbopergroup where " + where;
                 DataTable dtgroup = SqlHelper.GetDataTable(SqlHelper.connStr, sql);
                    for (int i = 0; i < count; i++)//逐周期处理
                    {
                        string field = i.ToString();
                        dtgroup.Columns.Add(field);//添加数据记录列
                        
                        //for (int j = dtgroup.Rows.Count-1; j>=0; j--)//倒序删除不纳入统计的工序组
                        //{
                        //    if (Convert.ToInt32(dtgroup.Rows[j]["OperGroupID"]) > 2000)//周产能预警不统计预估工时
                        //    {
                        //        dtgroup.Rows.Remove(dtgroup.Rows[j]);
                        //    }
                        //}


                        foreach (DataRow item in dtgroup.Rows)
                        {
                            DateTime sdate, edate;
                            if (cycle == "week")
                            {
                                  sdate = startdate.AddDays(i * 7);//startdate.AddMonths(i);
                                  edate = sdate.AddDays(6);//sdate.AddMonths(1).AddDays(-1);
                            }
                            else {//月
                                  sdate = startdate.AddMonths(i);
                                  edate = sdate.AddMonths(1).AddDays(-1);                            
                            }
                           
                            if (!refresh)
                            {
                                //从数据库取值，首先判断数据库值是否过期！
                                string sqldb = string.Format("select value, updatetime from  tbOperValue  where department='" + department + "' and type='"+cycle+"' and opergroupid={0} and sdate='{1}' and removefinished= '{2}'  order by updatetime desc", Convert.ToInt32(item["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);
                                DataTable dt = SqlHelper.GetDataTable(SqlHelper.connStr, sqldb);
                                if (dt.Rows.Count > 0)
                                {//数据库中有历史计算值
                                    object updatetime = dt.Rows[0]["updatetime"];
                                    DateTime d1 = Convert.ToDateTime(updatetime);
                                    DateTime d2 = DateTime.Now;
                                    TimeSpan sp = d2.Subtract(d1);
                                    if (sp.Minutes < ValidTime)//信息未过期
                                    {
                                        item[field] = dt.Rows[0]["value"];
                                        continue;
                                    }
                                }
                            }
                            //计算产能并刷新数据库记录
                            item[field] = operValue(Convert.ToInt32(item["OperGroupID"]), sdate, edate, removefinished, department, schedule);//调用工时值计算主函数
                            // item[field] = operValue(Convert.ToInt32(item["OperGroupID"]), sdate, edate, removefinished);//调用工时值计算主函数
                            if (saveToDB)
                            {    //删除陈旧数据，插入新数据
                                string sqldel = string.Format("delete from  tbOperValue where type='"+cycle+"'and department='" + department + "' and opergroupid={0} and sdate='{1}' and removefinished='{2}'", Convert.ToInt32(item["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);
                                SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sqldel);
                                string sql3 = string.Format("insert into tbOperValue(type,sdate,opergroupid,value,updatetime,removefinished,department) values('"+cycle+"','{0}',{1},{2},'{3}','{4}','{5}')", sdate.ToString("yyyy-MM-dd"), Convert.ToInt32(item["OperGroupID"]), item[field], DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), removefinished, department);
                                SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sql3);
                            }
                        }
                    }
                    return dtgroup; 
#endregion
                }

                else//类型：不分工艺，包含【计划、委外、预估】,
                {
                    #region 类型：不分工艺
                   // string  where = " OperGroupID<2000 or ischeck=1";
                    string where = " ischeck=1";
                    string sql = "select OperGroupName,OperGroupID,DayTime,AlertValue from tbopergroup where " + where;
                    DataTable dtgroup = SqlHelper.GetDataTable(SqlHelper.connStr, sql);
                    for (int i = 0; i < count; i++)//逐周期处理
                    {
                        string field = i.ToString();
                        dtgroup.Columns.Add(field);//添加数据记录列
                        foreach (DataRow item in dtgroup.Rows)
                        {
                            DateTime sdate, edate;
                            if (cycle == "week")
                            {
                                  sdate = startdate.AddDays(i * 7);//startdate.AddMonths(i);
                                  edate = sdate.AddDays(6);//sdate.AddMonths(1).AddDays(-1);
                            }
                            else {//月
                                  sdate = startdate.AddMonths(i);
                                  edate = sdate.AddMonths(1).AddDays(-1);                            
                            }
                            if (!refresh)
                            {
                                //从数据库取值，首先判断数据库值是否过期！
                                string sqldb = string.Format("select value, updatetime from  tbOperValue  where department='" + department + "' and type='"+cycle+"' and opergroupid={0} and sdate='{1}' and removefinished= '{2}'  order by updatetime desc", Convert.ToInt32(item["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);
                                DataTable dt = SqlHelper.GetDataTable(SqlHelper.connStr, sqldb);
                                if (dt.Rows.Count > 0)
                                {//数据库中有历史计算值
                                    object updatetime = dt.Rows[0]["updatetime"];
                                    DateTime d1 = Convert.ToDateTime(updatetime);
                                    DateTime d2 = DateTime.Now;
                                    TimeSpan sp = d2.Subtract(d1);
                                    if (sp.Minutes < ValidTime)//信息未过期
                                    {
                                        item[field] = dt.Rows[0]["value"];
                                        continue;
                                    }
                                }
                            }
                            //计算产能并刷新数据库记录
                            item[field] = operValue(Convert.ToInt32(item["OperGroupID"]), sdate, edate, removefinished, department, schedule);//调用工时值计算主函数
                           
                            if (saveToDB)
                            {    //删除陈旧数据，插入新数据
                            string sqldel = string.Format("delete from  tbOperValue where type='"+cycle+"'and department='" + department + "' and opergroupid={0} and sdate='{1}' and removefinished='{2}'", Convert.ToInt32(item["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);
                            SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sqldel);
                            string sql3 = string.Format("insert into tbOperValue(type,sdate,opergroupid,value,updatetime,removefinished,department) values('"+cycle+"','{0}',{1},{2},'{3}','{4}','{5}')", sdate.ToString("yyyy-MM-dd"), Convert.ToInt32(item["OperGroupID"]), item[field], DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), removefinished, department);
                            SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sql3);
                            }
                        }
                    }
                    return dtgroup;  
                    #endregion
                }
             
        }

        //public static DataTable getOperEstWorkTime(string cycle, DateTime startdate, int count, bool refresh, bool removefinished, string department, string schedule, bool classifyOper)
        //{
        //    bool saveToDB = true;
        //    if (schedule == "autocal")//选取的自动排程
        //    {
        //        refresh = true;
        //        saveToDB = false;
        //    }
        //    if (classifyOper)//类型：分工艺
        //    {
        //        #region 类型：分工艺
        //        string where = " OperGroupID<2000 and ischeck=1";
        //        string sql = "select OperGroupName,OperGroupID,DayTime,AlertValue from tbopergroup where " + where;
        //        DataTable dtgroup = SqlHelper.GetDataTable(SqlHelper.connStr, sql);
        //        for (int i = 0; i < count; i++)//逐周期处理
        //        {
        //            string field = i.ToString();
        //            dtgroup.Columns.Add(field);//添加数据记录列
        //            for (int j = dtgroup.Rows.Count - 1; j >= 0; j--)//倒序删除不纳入统计的工序组
        //            {
        //                if (Convert.ToInt32(dtgroup.Rows[j]["OperGroupID"]) > 2000)//周产能预警不统计预估工时
        //                {
        //                    dtgroup.Rows.Remove(dtgroup.Rows[j]);
        //                }
        //            }
        //            foreach (DataRow item in dtgroup.Rows)
        //            {
        //                DateTime sdate, edate;
        //                if (cycle == "week")
        //                {
        //                    sdate = startdate.AddDays(i * 7);//startdate.AddMonths(i);
        //                    edate = sdate.AddDays(6);//sdate.AddMonths(1).AddDays(-1);
        //                }
        //                else
        //                {//月
        //                    sdate = startdate.AddMonths(i);
        //                    edate = sdate.AddMonths(1).AddDays(-1);
        //                }

        //                if (!refresh)
        //                {
        //                    //从数据库取值，首先判断数据库值是否过期！
        //                    string sqldb = string.Format("select value, updatetime from  tbOperValue  where department='" + department + "' and type='" + cycle + "' and opergroupid={0} and sdate='{1}' and removefinished= '{2}'  order by updatetime desc", Convert.ToInt32(item["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);
        //                    DataTable dt = SqlHelper.GetDataTable(SqlHelper.connStr, sqldb);
        //                    if (dt.Rows.Count > 0)
        //                    {//数据库中有历史计算值
        //                        object updatetime = dt.Rows[0]["updatetime"];
        //                        DateTime d1 = Convert.ToDateTime(updatetime);
        //                        DateTime d2 = DateTime.Now;
        //                        TimeSpan sp = d2.Subtract(d1);
        //                        if (sp.Minutes < ValidTime)//信息未过期
        //                        {
        //                            item[field] = dt.Rows[0]["value"];
        //                            continue;
        //                        }
        //                    }
        //                }
        //                //计算产能并刷新数据库记录
        //                item[field] = operValue(Convert.ToInt32(item["OperGroupID"]), sdate, edate, removefinished, department, schedule);//调用工时值计算主函数
        //                // item[field] = operValue(Convert.ToInt32(item["OperGroupID"]), sdate, edate, removefinished);//调用工时值计算主函数
        //                if (saveToDB)
        //                {    //删除陈旧数据，插入新数据
        //                    string sqldel = string.Format("delete from  tbOperValue where type='" + cycle + "'and department='" + department + "' and opergroupid={0} and sdate='{1}' and removefinished='{2}'", Convert.ToInt32(item["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);
        //                    SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sqldel);
        //                    string sql3 = string.Format("insert into tbOperValue(type,sdate,opergroupid,value,updatetime,removefinished,department) values('" + cycle + "','{0}',{1},{2},'{3}','{4}','{5}')", sdate.ToString("yyyy-MM-dd"), Convert.ToInt32(item["OperGroupID"]), item[field], DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), removefinished, department);
        //                    SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sql3);
        //                }
        //            }
        //        }
        //        return dtgroup;
        //        #endregion
        //    }

        //    else//类型：计划、委外、预估
        //    {
        //        #region 类型：不分工艺
        //        string where = " OperGroupID<2000 or ischeck=1";
        //        string sql = "select OperGroupName,OperGroupID,DayTime,AlertValue from tbopergroup where " + where;
        //        DataTable dtgroup = SqlHelper.GetDataTable(SqlHelper.connStr, sql);
        //        for (int i = 0; i < count; i++)//逐周期处理
        //        {
        //            string field = i.ToString();
        //            dtgroup.Columns.Add(field);//添加数据记录列
        //            foreach (DataRow item in dtgroup.Rows)
        //            {
        //                DateTime sdate, edate;
        //                if (cycle == "week")
        //                {
        //                    sdate = startdate.AddDays(i * 7);//startdate.AddMonths(i);
        //                    edate = sdate.AddDays(6);//sdate.AddMonths(1).AddDays(-1);
        //                }
        //                else
        //                {//月
        //                    sdate = startdate.AddMonths(i);
        //                    edate = sdate.AddMonths(1).AddDays(-1);
        //                }
        //                if (!refresh)
        //                {
        //                    //从数据库取值，首先判断数据库值是否过期！
        //                    string sqldb = string.Format("select value, updatetime from  tbOperValue  where department='" + department + "' and type='" + cycle + "' and opergroupid={0} and sdate='{1}' and removefinished= '{2}'  order by updatetime desc", Convert.ToInt32(item["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);
        //                    DataTable dt = SqlHelper.GetDataTable(SqlHelper.connStr, sqldb);
        //                    if (dt.Rows.Count > 0)
        //                    {//数据库中有历史计算值
        //                        object updatetime = dt.Rows[0]["updatetime"];
        //                        DateTime d1 = Convert.ToDateTime(updatetime);
        //                        DateTime d2 = DateTime.Now;
        //                        TimeSpan sp = d2.Subtract(d1);
        //                        if (sp.Minutes < ValidTime)//信息未过期
        //                        {
        //                            item[field] = dt.Rows[0]["value"];
        //                            continue;
        //                        }
        //                    }
        //                }
        //                //计算产能并刷新数据库记录
        //                item[field] = operValue(Convert.ToInt32(item["OperGroupID"]), sdate, edate, removefinished, department, schedule);//调用工时值计算主函数

        //                if (saveToDB)
        //                {    //删除陈旧数据，插入新数据
        //                    string sqldel = string.Format("delete from  tbOperValue where type='" + cycle + "'and department='" + department + "' and opergroupid={0} and sdate='{1}' and removefinished='{2}'", Convert.ToInt32(item["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);
        //                    SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sqldel);
        //                    string sql3 = string.Format("insert into tbOperValue(type,sdate,opergroupid,value,updatetime,removefinished,department) values('" + cycle + "','{0}',{1},{2},'{3}','{4}','{5}')", sdate.ToString("yyyy-MM-dd"), Convert.ToInt32(item["OperGroupID"]), item[field], DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), removefinished, department);
        //                    SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sql3);
        //                }
        //            }
        //        }
        //        return dtgroup;
        //        #endregion
        //    }

        //}


        /// <summary>
        /// 目前仅用于周产能预警查询
        /// </summary>
        /// <param name="startdate"></param>
        /// <param name="count"></param>
        /// <param name="type"></param>
        /// <param name="refresh"></param>
        /// <param name="removefinished"></param>
        /// <returns></returns>
        //public DataTable getOperAlertDataTable(DateTime startdate, int count, string type, bool refresh, bool removefinished)
        //{
        //    //获取工艺组相关配置数据：
        //    string sql = " select OperGroupName,OperGroupID,DayTime,AlertValue from tbopergroup where OperGroupID<2000 and ischeck=1";
        //    DataTable dtgroup = SqlHelper.GetDataTable(SqlHelper.connStr, sql);
        //    #region
        //    if (type == "week")
        //    {

        //        // int year = DateTime.Now.Year;
        //        for (int i = 0; i < count; i++)
        //        {
        //            string field = i.ToString();
        //            dtgroup.Columns.Add(field);

        //            // foreach (DataRow item in dtgroup.Rows)
        //            for (int j = 0; j < dtgroup.Rows.Count; j++)
        //            {

        //                if (Convert.ToInt32(dtgroup.Rows[j]["OperGroupID"]) > 2000)//周产能预警不统计预估工时
        //                {
        //                    dtgroup.Rows.Remove(dtgroup.Rows[j]);
        //                    continue;
        //                }
        //                DateTime sdate = startdate.AddDays(i * 7);
        //                DateTime edate = sdate.AddDays(6);
        //                bool cal = false;
        //                if (refresh)
        //                {
        //                    cal = true;
        //                }
        //                else
        //                {
        //                    //从数据库取值，首先判断数据库值是否过期！
        //                    string sqldb = string.Format("select updatetime,value from tbOperValue where type='week' and opergroupid={0} and sdate='{1}' and removefinished='{2}'", Convert.ToInt32(dtgroup.Rows[j]["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);
        //                    DataTable dt = SqlHelper.GetDataTable(SqlHelper.connStr, sqldb);
        //                    if (dt.Rows.Count == 0)
        //                    {
        //                        cal = true;
        //                    }
        //                    else
        //                    {
        //                        object updatetime = dt.Rows[0]["updatetime"];
        //                        DateTime d1 = Convert.ToDateTime(updatetime);
        //                        DateTime d2 = DateTime.Now;
        //                        TimeSpan sp = d2.Subtract(d1);
        //                        if (sp.Minutes >= validTime)//信息过期，过期时间24小时，后期改成xml配置；
        //                        {
        //                            cal = true;
        //                        }
        //                        else
        //                        {
        //                            // string sqlvalue = string.Format("select value from tbOperValue where type='week' and opergroupid={0} and sdate='{1}'and removefinished={2}", Convert.ToInt32(dtgroup.Rows[j]["OperGroupID"]), sdate.ToString("yyyy-MM-dd"));
        //                            //float value = Convert.ToSingle(SqlHelper.ExecuteScalar(SqlHelper.connStr, sqlvalue));
        //                            //dtgroup.Rows[j][field] = operValue(Convert.ToInt32(dtgroup.Rows[j]["OperGroupID"]), sdate, edate);
        //                            dtgroup.Rows[j][field] = dt.Rows[0]["value"];

        //                        }
        //                    }
        //                }
        //                if (cal)//从原始数据进行计算
        //                {
        //                    dtgroup.Rows[j][field] = operValue(Convert.ToInt32(dtgroup.Rows[j]["OperGroupID"]), sdate, edate, removefinished);
        //                    //删除陈旧数据，插入新数据
        //                    //string sqldel = string.Format("delete tbOperValue where type='week' and opergroupid={0} and sdate='{1}' and removefinished='{2}'", Convert.ToInt32(dtgroup.Rows[j]["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);
        //                    string sqldel = string.Format("delete from tbOperValue where type='week' and opergroupid={0} and sdate='{1}' and removefinished='{2}'", Convert.ToInt32(dtgroup.Rows[j]["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);
        //                    SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sqldel);
        //                    string sql3 = string.Format("insert into tbOperValue(type,sdate,opergroupid,value,updatetime,removefinished) values('week','{0}',{1},{2},'{3}','{4}')", sdate.ToString("yyyy-MM-dd"), Convert.ToInt32(dtgroup.Rows[j]["OperGroupID"]), dtgroup.Rows[j][field], DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), removefinished);
        //                    SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sql3);

        //                }
        //            }
        //        }

        //    }
        //    #endregion
        //    #region
        //    else if (type == "month2")
        //    {

        //        for (int i = 0; i < count; i++)
        //        {
        //            string field = i.ToString();
        //            dtgroup.Columns.Add(field);
        //            foreach (DataRow item in dtgroup.Rows)
        //            {
        //                DateTime sdate = startdate.AddMonths(i);
        //                DateTime edate = sdate.AddMonths(1).AddDays(-1);
        //                bool cal = false;
        //                if (refresh)
        //                {
        //                    cal = true;
        //                }
        //                else
        //                {
        //                    //从数据库取值，首先判断数据库值是否过期！
        //                    string sqldb = string.Format("select value, updatetime from tbOperValue where type='month2' and opergroupid={0} and sdate='{1}' and removefinished= '{2}'", Convert.ToInt32(item["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);
        //                    DataTable dt = SqlHelper.GetDataTable(SqlHelper.connStr, sqldb);

        //                    if (dt.Rows.Count == 0)
        //                    {
        //                        cal = true;
        //                    }
        //                    else
        //                    {
        //                        object updatetime = dt.Rows[0]["updatetime"];
        //                        DateTime d1 = Convert.ToDateTime(updatetime);
        //                        DateTime d2 = DateTime.Now;
        //                        TimeSpan sp = d2.Subtract(d1);
        //                        if (sp.Minutes >= validTime)//信息过期
        //                        {
        //                            cal = true;
        //                        }
        //                        else
        //                        {
        //                            item[field] = dt.Rows[0]["value"];
        //                            //string sqlvalue = string.Format("select value from tbOperValue where type='month' and opergroupid={0} and sdate='{1}'", Convert.ToInt32(item["OperGroupID"]), sdate.ToString("yyyy-MM-dd"));
        //                            //float value = Convert.ToSingle(SqlHelper.ExecuteScalar(SqlHelper.connStr, sqlvalue));
        //                            //item[field] = operValue(Convert.ToInt32(item["OperGroupID"]), sdate, edate); 
        //                        }
        //                    }
        //                }
        //                if (cal)
        //                {
        //                    item[field] = operValue(Convert.ToInt32(item["OperGroupID"]), sdate, edate, removefinished);//调用工时值计算主函数
        //                    //删除陈旧数据，插入新数据
        //                    string sqldel = string.Format("delete  from tbOperValue where type='month2' and opergroupid={0} and sdate='{1}' and removefinished='{2}'", Convert.ToInt32(item["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);//20190301
        //                    SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sqldel);
        //                    string sql3 = string.Format("insert into tbOperValue(type,sdate,opergroupid,value,updatetime,removefinished) values('month2','{0}',{1},{2},'{3}','{4}')", sdate.ToString("yyyy-MM-dd"), Convert.ToInt32(item["OperGroupID"]), item[field], DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), removefinished);// 20190301
        //                    SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sql3);

        //                    ////查询是否有记录
        //                    //string sql1 = string.Format("select count(*) from tbOperValue where type='month' and opergroupid={0} and sdate='{1}'", Convert.ToInt32(item["OperGroupID"]), sdate.ToString("yyyy-MM-dd"));
        //                    //if (Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.connStr, sql1)) > 0)//有记录则更新
        //                    //{
        //                    //    string sql2 = string.Format("update tbOperValue set value={0},updatetime='{1}' where type='month' and opergroupid='{2}' and sdate='{3}'", item[field], DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), Convert.ToInt32(item["OperGroupID"]), sdate.ToString("yyyy-MM-dd"));
        //                    //    SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sql1);
        //                    //}
        //                    //else
        //                    //{
        //                    //    string sql3 = string.Format("insert into tbOperValue(type,sdate,opergroupid,value,updatetime) values('month','{0}',{1},{2},'{3}')", sdate.ToString("yyyy-MM-dd"), Convert.ToInt32(item["OperGroupID"]), item[field], DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        //                    //    SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sql3);
        //                    //}
        //                }
        //            }
        //        }
        //        return dtgroup;
        //    }
        //    #endregion

        //    return dtgroup;
        //}
        //public DataTable getOperAlertDataTable(DateTime startdate, int count, string type, bool refresh, bool removefinished, string tablename)
        //{
        //    //获取工艺组相关配置数据：
        //    string sql = " select OperGroupName,OperGroupID,DayTime,AlertValue from tbopergroup where OperGroupID<2000 and ischeck=1";
        //    DataTable dtgroup = SqlHelper.GetDataTable(SqlHelper.connStr, sql);

        //    if (type == "week")
        //    {
        //        for (int i = 0; i < count; i++)
        //        {
        //            string field = i.ToString();
        //            dtgroup.Columns.Add(field);

        //            // foreach (DataRow item in dtgroup.Rows)
        //            for (int j = 0; j < dtgroup.Rows.Count; j++)
        //            {

        //                if (Convert.ToInt32(dtgroup.Rows[j]["OperGroupID"]) > 2000)//周产能预警不统计预估工时
        //                {
        //                    dtgroup.Rows.Remove(dtgroup.Rows[j]);
        //                    continue;
        //                }
        //                DateTime sdate = startdate.AddDays(i * 7);
        //                DateTime edate = sdate.AddDays(6);
        //                bool cal = false;
        //                if (refresh)
        //                {
        //                    cal = true;
        //                }
        //                else
        //                {
        //                    //从数据库取值，首先判断数据库值是否过期！
        //                    string sqldb = string.Format("select updatetime,value from " + tablename + " where type='week' and opergroupid={0} and sdate='{1}' and removefinished='{2}'", Convert.ToInt32(dtgroup.Rows[j]["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);
        //                    DataTable dt = SqlHelper.GetDataTable(SqlHelper.connStr, sqldb);
        //                    if (dt.Rows.Count == 0)
        //                    {
        //                        cal = true;
        //                    }
        //                    else
        //                    {
        //                        object updatetime = dt.Rows[0]["updatetime"];
        //                        DateTime d1 = Convert.ToDateTime(updatetime);
        //                        DateTime d2 = DateTime.Now;
        //                        TimeSpan sp = d2.Subtract(d1);
        //                        if (sp.Minutes >= validTime)//信息过期，过期时间24小时，后期改成xml配置；
        //                        {
        //                            cal = true;
        //                        }
        //                        else
        //                        {

        //                            dtgroup.Rows[j][field] = dt.Rows[0]["value"];

        //                        }
        //                    }
        //                }
        //                if (cal)//从原始数据进行计算
        //                {
        //                    dtgroup.Rows[j][field] = operValue2(Convert.ToInt32(dtgroup.Rows[j]["OperGroupID"]), sdate, edate, removefinished, tablename);
        //                    // dtgroup.Rows[j][field] = operValue(Convert.ToInt32(dtgroup.Rows[j]["OperGroupID"]), sdate, edate, removefinished);
        //                    //删除陈旧数据，插入新数据
        //                    string sqldel = string.Format("delete from " + tablename + " where type='week' and opergroupid={0} and sdate='{1}' and removefinished='{2}'", Convert.ToInt32(dtgroup.Rows[j]["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);
        //                    SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sqldel);
        //                    string sql3 = string.Format("insert into " + tablename + "(type,sdate,opergroupid,value,updatetime,removefinished) values('week','{0}',{1},{2},'{3}','{4}')", sdate.ToString("yyyy-MM-dd"), Convert.ToInt32(dtgroup.Rows[j]["OperGroupID"]), dtgroup.Rows[j][field], DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), removefinished);
        //                    SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sql3);

        //                }
        //            }
        //        }

        //    }

        //    #region
        //    else if (type == "month2")
        //    {

        //        for (int i = 0; i < count; i++)
        //        {
        //            string field = i.ToString();
        //            dtgroup.Columns.Add(field);
        //            foreach (DataRow item in dtgroup.Rows)
        //            {
        //                DateTime sdate = startdate.AddMonths(i);
        //                DateTime edate = sdate.AddMonths(1).AddDays(-1);
        //                bool cal = false;
        //                if (refresh)
        //                {
        //                    cal = true;
        //                }
        //                else
        //                {
        //                    //从数据库取值，首先判断数据库值是否过期！
        //                    string sqldb = string.Format("select value, updatetime from " + tablename + " where type='month2' and opergroupid={0} and sdate='{1}' and removefinished= '{2}'", Convert.ToInt32(item["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);
        //                    DataTable dt = SqlHelper.GetDataTable(SqlHelper.connStr, sqldb);

        //                    if (dt.Rows.Count == 0)
        //                    {
        //                        cal = true;
        //                    }
        //                    else
        //                    {
        //                        object updatetime = dt.Rows[0]["updatetime"];
        //                        DateTime d1 = Convert.ToDateTime(updatetime);
        //                        DateTime d2 = DateTime.Now;
        //                        TimeSpan sp = d2.Subtract(d1);
        //                        if (sp.Minutes >= validTime)//信息过期
        //                        {
        //                            cal = true;
        //                        }
        //                        else
        //                        {
        //                            item[field] = dt.Rows[0]["value"];

        //                        }
        //                    }
        //                }
        //                if (cal)
        //                {
        //                    item[field] = operValue2(Convert.ToInt32(item["OperGroupID"]), sdate, edate, removefinished, tablename);//调用工时值计算主函数
        //                    //删除陈旧数据，插入新数据
        //                    //item[field] = operValue(Convert.ToInt32(item["OperGroupID"]), sdate, edate, removefinished);//调用工时值计算主函数
        //                    //删除陈旧数据，插入新数据
        //                    string sqldel = string.Format("delete  from " + tablename + " where type='month2' and opergroupid={0} and sdate='{1}' and removefinished='{2}'", Convert.ToInt32(item["OperGroupID"]), sdate.ToString("yyyy-MM-dd"), removefinished);//20190301
        //                    SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sqldel);
        //                    string sql3 = string.Format("insert into " + tablename + "(type,sdate,opergroupid,value,updatetime,removefinished) values('month2','{0}',{1},{2},'{3}','{4}')", sdate.ToString("yyyy-MM-dd"), Convert.ToInt32(item["OperGroupID"]), item[field], DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), removefinished);// 20190301
        //                    SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sql3);

        //                }
        //            }
        //        }
        //        return dtgroup;
        //    }
        //    #endregion

        //    return dtgroup;
        //}
    }

 
}
